# -*-coding:utf8-*-

import pymysql
import json
import time
import requests


class Database:
    def __init__(self, host, account, password, database_name, port) -> None:
        self.host = host
        self.account = account
        self.password = password
        self.database = database_name
        self.port = port

    def getFollowList(self):
        '''
        返回数据库中所有用户的关注字典
        '''

        # 创建连接
        db = pymysql.connect(host = self.host, user = self.account, password = self.password, database = self.database, port = self.port)
        cursor = db.cursor()
        sql = '''
            select uid, user_qq from follow 
        '''
        try:
            cursor.execute(sql)
            res = cursor.fetchall()
        except:
            print('查询数据库时失败')
        finally:
            db.close()
        result = {}
        for uid, user_qq in res:
            if uid in result:
                result[uid].append(user_qq)
            else:
                result[uid] = [user_qq]
        return result



    def insertDynamic(self, dynamic_id, content:dict):
        '''
        向数据库中插入动态
        '''
        values = (dynamic_id, json.dumps(content))
        sql = '''
            insert into dynamic(uid, content)
            values(%s, %s)
        '''
        # 创建连接
        db = pymysql.connect(host = self.host, user = self.account, password = self.password, database = self.database, port = self.port)
        cursor = db.cursor()

        status = False

        try:
            # 执行sql命令
            cursor.execute(sql, values)
            db.commit()
            status = True
        except:
            print('插入动态失败')
            db.rollback()
        finally:
            db.close()
        return status

    

    def selectDynamic(self, dynamic_id):
        '''
        在数据库中查找是否有对应动态
        '''
        values = (dynamic_id)
        sql = '''
            select *
            from dynamic
            where uid=%s
        '''
        # 创建连接
        db = pymysql.connect(host = self.host, user = self.account, password = self.password, database = self.database, port = self.port)
        cursor = db.cursor()
        status = False
        try:
            cursor.execute(sql, values)
            res = cursor.fetchall()
            if res:
                status = True
        except:
            print('执行sql语句时出错')
            db.rollback()
        finally:
            db.close()
        return status



    def getUserName(self, uid):
        '''
        将用户名称存入数据库中，使用时从数据库中取出，防止多次访问api导致无法获取数据
        '''
        values = (uid)
        sql = '''
            select name
            from uidtoname
            where uid=%s
        '''
        # 创建连接
        db = pymysql.connect(host = self.host, user = self.account, password = self.password, database = self.database, port = self.port)
        cursor = db.cursor()
        name = ''
        try:
            cursor.execute(sql, values)
            res = cursor.fetchall()
            if res:
                name = res[0][0]
            else:
                # 不在数据库中要重新存入
                # 一秒延迟防止访问不到数据
                time.sleep(1)

                res = requests.get(f'https://api.bilibili.com/x/space/acc/info?mid={str(uid)}&jsonp=jsonp')#拼接完整链接
                res.encoding = 'utf-8'
                data = json.loads(res.text)#使用json方式
                try:
                    name = data['data']['name']#得到用户名
                except:
                    print(data)
                
                # 获取之后要放到数据库中
                sql = '''
                    insert into uidtoname(uid, name)
                    values(%s, %s)
                '''
                values = (uid, name)
                cursor.execute(sql, values)
                db.commit()
        except:
            print('获取用户名称时出错')
            db.rollback()
        finally:
            db.close()
        return name


    def getLiveFollowList(self):
        '''
        从数据库中获取关注直播字典
        '''
        # 创建连接
        db = pymysql.connect(host = self.host, user = self.account, password = self.password, database = self.database, port = self.port)
        cursor = db.cursor()
        sql = '''
            select uid, user_qq from follow_live 
        '''
        try:
            cursor.execute(sql)
            res = cursor.fetchall()
        except:
            print('查询数据库时失败')
        finally:
            db.close()
        result = {}
        for uid, user_qq in res:
            if uid in result:
                result[uid].append(user_qq)
            else:
                result[uid] = [user_qq]
        return result
    


    def insertDynamicFollow(self, uid, user_qq):
        '''
        向数据库中添加关注
        '''
        status = False
        
        # 创建连接
        db = pymysql.connect(host = self.host, user = self.account, password = self.password, database = self.database, port = self.port)
        cursor = db.cursor()
        sql = '''
            insert into follow(uid, user_qq)
            values(%s, %s)
        '''
        try:
            values = (uid, user_qq)
            cursor.execute(sql, values)
            db.commit()
            status = True
        except:
            print('查询数据库时失败')
        finally:
            db.close()
        return status